How to Add a Not Null Constraint in BigQuery

SQL Tutorial Home

In Bigquery you can’t alter an existing NULLABLE column to NOT NULL. If you need to do that (I presume all the rows have a not null value in the field you want to change to NOT NULL). Then you can create a new table with other name defining the column as NOT NULL, and copy all data from the original table to the new table. Finally drop the original table and alter table name. Below is an example:

create table Database_test_IB1.test_not_null(
id integer not null, 
description string
);
-- insert data into test_not_null

create table Database_test_IB1.test_not_null_v2(
id integer not null, 
description string not null
);
-- copy data from original table to new table
insert into Database_test_IB1.test_not_null_v2 
 select * from Database_test_IB1.test_not_null;

-- drop original table and rename new table
drop table Database_test_IB1.test_not_null;
alter table Database_test_IB1.test_not_null_v2 rename to test_not_null;

IN THIS PAGE